﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Lg.Core.Extensions;
using Lg.Core.Extensions.DbHelper;

namespace Lg.Core.DbHelper
{
    public class SqlHelper:IDBHelper
    {
        public string _readCnnStr { get ; set ; }
        public string _writeCnnStr { get ; set; }

        #region 构造方法
        public SqlHelper(IJsonHelper? jsonHelper=null)
        {
            if (jsonHelper == null)
            {
                jsonHelper=new JsonHelper();
            }
            _readCnnStr =jsonHelper.GetValue("DataBase.SqlConnectionString.Read");
            _writeCnnStr = jsonHelper.GetValue("DataBase.SqlConnectionString.Write");
        }
        public SqlHelper(string readCnnStr,string? writeCnnstr=null)
        {
            _readCnnStr = readCnnStr;
            _writeCnnStr = writeCnnstr ?? readCnnStr;
        }
        #endregion

        #region[sql 原生方法]
        /// <summary>
        /// 增，删 ，改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            using (SqlConnection cnn = new SqlConnection(_writeCnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cnn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 有参数的 增，删 ，改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, List<DBParameter>? listParam=null)
        {
            using (SqlConnection cnn = new SqlConnection(_writeCnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                if (listParam != null)
                {
                    cmd.Parameters.AddRange(listParam.ToListSqlParam().ToArray());
                }
                cnn.Open();
                return cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 返回1行一列，无数据则为null,最大长度为2033个字符
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="listParam"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, List<DBParameter>? listParam=null)
        {
            using (SqlConnection cnn = new SqlConnection(_readCnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                if (listParam != null)
                { 
                    cmd.Parameters.AddRange(listParam.ToListSqlParam().ToArray()); 
                }
                cnn.Open();
                return cmd.ExecuteScalar();
            }
        }

        public DataSet Query(string sql, List<DBParameter>? listParam=null)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cnn = new SqlConnection(_readCnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                if(listParam != null)
                {
                    cmd.Parameters.AddRange(listParam.ToListSqlParam().ToArray());
                }
                cnn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        #endregion
    }
}
